package ru.minidoc.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.List;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;

import ru.minidoc.model.Document;
import ru.minidoc.model.User;

public class DocumentDao extends AbstractDao {
    public Document load(final Document document) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        Document result = jdbcTemplate.query(
                "select document.id, document.name, document.assignedTo, document.modified, document.projectId,"
                + " document.modifiedBy, user.fullname, project.name, document.text from document"
                + " left join user on user.id = document.assignedTo"
                + " left join project on project.id = document.projectId"
                + " where document.id = ?",
                new Object[] { document.getId() },
                new ResultSetExtractor<Document>() {

                    @Override
                    public Document extractData(ResultSet rs)
                            throws SQLException, DataAccessException {
                        if (rs.next()) {
                            Document document = new Document();
                            document.setId(rs.getInt(1));
                            document.setName(rs.getString(2));
                            document.setAssignedToId(rs.getInt(3));
                            document.setModified(new SimpleDateFormat()
                                    .format(rs.getTimestamp(4)));
                            document.setProjectId(rs.getInt(5));
                            document.setModifiedBy(rs.getString(6));
                            document.setAssignedTo(rs.getString(7));
                            document.setProject(rs.getString(8));
                            document.setText(rs.getString(9));
                            return document;
                        }
                        return document;
                    }
                });
        return result;
    }

    public void saveText(Document document, User user) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        int version = jdbcTemplate.queryForInt("select max(version) from documenthistory where documentId = ?", new Object[] {document.getId()});
        version++;
        jdbcTemplate.update(
                "insert into documenthistory(documentId, name, assignedTo, modified, projectId, modifiedBy, text, version) values (?,?,?,now(), ?, ?, ?, ?)",
                new Object[] {document.getId(), document.getName(), document.getAssignedToId(),
                        document.getProjectId(), user.getFullname(), document.getText(), version });
        jdbcTemplate.update(
                "update document set text = ?, modifiedBy=?, modified=now(), version = ? where id = ?",
                new Object[] { document.getText(), user.getFullname(), version, document.getId() });
    }

    public int create(Document document, User user) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.update(
                "insert into documenthistory(name, assignedTo, modified, projectId, modifiedBy, version) values (?,?,now(), ?, ?, 1)",
                new Object[] { document.getName(), document.getAssignedToId(),
                        document.getProjectId(), user.getFullname() });
        return queryAndReturnIdentity(
                "insert into document(name, assignedTo, modified, projectId, modifiedBy, version) values (?,?,now(), ?, ?, 1)",
                new Object[] { document.getName(), document.getAssignedToId(),
                        document.getProjectId(), user.getFullname() });
    }

    public void delete(Document document) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.update("delete from document where id = ?",
                new Object[] { document.getId() });
        jdbcTemplate.update("delete from documenthistory where documentId = ?",
                new Object[] { document.getId() });
    }
    public List<Document> getDocumentList() {
        return getDocumentList(null);
    }
    public List<Document> getDocumentList(User user) {
        String whereClause = "";
        if ((user != null) && (!user.isManager())) {
            whereClause = " where user.id = " + user.getId();
        }
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        List<Document> result = jdbcTemplate
                .query("select document.id, document.name, document.assignedTo, document.modified, document.projectId,"
                        + " document.modifiedBy, user.fullname, project.name, document.version from document"
                        + " left join user on user.id = document.assignedTo"
                        + " left join project on project.id = document.projectId"
                        + whereClause
                        ,
                        new RowMapper<Document>() {

                            @Override
                            public Document mapRow(ResultSet rs, int rowNum)
                                    throws SQLException {
                                Document document = new Document();
                                document.setId(rs.getInt(1));
                                document.setName(rs.getString(2));
                                document.setAssignedToId(rs.getInt(3));
                                document.setModified(new SimpleDateFormat()
                                        .format(rs.getTimestamp(4)));
                                document.setProjectId(rs.getInt(5));
                                document.setModifiedBy(rs.getString(6));
                                document.setAssignedTo(rs.getString(7));
                                document.setProject(rs.getString(8));
                                document.setVersion(rs.getInt(9));
                                return document;
                            }
                        });
        return result;
    }

    public void update(Document document) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate
                .update("update document set name = ?, assignedToId=?, projectId=?, modified=now() where id = ?",
                        new Object[] { document.getName(),
                                document.getAssignedToId(),
                                document.getProjectId(), document.getId() });
    }
}
